package com.hgf.helper.mybatisplusdemo.db.service;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hgf.helper.mybatisplus.BaseServiceImpl;
import com.hgf.helper.mybatisplus.OriginSqlBo;
import com.hgf.helper.mybatisplus.PageParam;
import com.hgf.helper.mybatisplus.join.JoinEnum;
import com.hgf.helper.mybatisplus.join.JoinLambdaQueryWrapper;
import com.hgf.helper.mybatisplus.join.JoinQueryBuilder;
import com.hgf.helper.mybatisplusdemo.db.entity.CardInfo;
import com.hgf.helper.mybatisplusdemo.db.entity.RewardInfo;
import com.hgf.helper.mybatisplusdemo.db.entity.ScoreInfo;
import com.hgf.helper.mybatisplusdemo.db.entity.Student;
import com.hgf.helper.mybatisplusdemo.db.mapper.StudentMapper;
import com.hgf.helper.mybatisplusdemo.pojo.vo.ResultVo;
import org.springframework.stereotype.Service;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class StudentServiceImpl extends BaseServiceImpl<StudentMapper, Student> {


    /**
     * 联查多条记录测试
     */
    public List<Student> joinListTest() {
        // 主表wrapper
        JoinLambdaQueryWrapper<Student> studentWrapper = joinQueryWrapperForTable();
        // 副表wrapper
        JoinLambdaQueryWrapper<ScoreInfo> scoreWrapper = studentWrapper.join(
                JoinQueryBuilder.<ScoreInfo, Student, Student>builder()
                        .joinEnum(JoinEnum.INNER)
                        .joinColumnAnnFiled(Student::getScore)
                        .build()
        );

        JoinLambdaQueryWrapper<RewardInfo> rewardWrapper = studentWrapper.join(
                JoinQueryBuilder.<RewardInfo,Student,ResultVo>builder()
                        .joinEnum(JoinEnum.LEFT)
                        .mainJoinField(Student::getNo)
                        .joinField(RewardInfo::getStuNo)
                        .build()
        );

        // 查询指定字段
//        studentWrapper.select(Student::getNo);
//        scoreWrapper.select(ScoreInfo::getScore, ScoreInfo::getStuScore);
        // 查询条件
        studentWrapper.in(Student::getId, Arrays.asList(2, 13));

        // 排序
        scoreWrapper.orderByDesc(ScoreInfo::getScore);

        return baseMapper.selectJoinList(studentWrapper);
    }

    /**
     * 联查单条测试
     */
    public Student joinOneTest() {
        // 主表wrapper
        JoinLambdaQueryWrapper<Student> studentWrapper = joinQueryWrapperForTable();
        // 副表wrapper
        JoinLambdaQueryWrapper<ScoreInfo> scoreWrapper = studentWrapper.join(
                JoinQueryBuilder.<ScoreInfo, Student, Student>builder()
                        .joinEnum(JoinEnum.INNER)
                        .joinColumnAnnFiled(Student::getScore)
                        .build()
        );

        JoinLambdaQueryWrapper<RewardInfo> rewardWrapper = studentWrapper.join(
                JoinQueryBuilder.<RewardInfo,Student,ResultVo>builder()
                        .joinEnum(JoinEnum.LEFT)
                        .mainJoinField(Student::getNo)
                        .joinField(RewardInfo::getStuNo)
                        .build()
        );

        // 查询指定字段
//        studentWrapper.select(Student::getNo);
//        scoreWrapper.select(ScoreInfo::getScore, ScoreInfo::getStuScore);
        // 查询条件
        studentWrapper.eq(Student::getId, 2);

        // 排序
//        studentWrapper.orderByAsc(Student::getSex);

        return baseMapper.selectJoinOne(studentWrapper);
    }

    /**
     * 联查单条测试，返回为其他类
     */
    public ResultVo joinOneOtherClassTest() {
        // 主表wrapper
        JoinLambdaQueryWrapper<Student> studentWrapper = joinQueryWrapperForOther(ResultVo.class);
        // 副表wrapper
        studentWrapper.join(
                JoinQueryBuilder.<CardInfo, Student, ResultVo>builder()
                        .joinEnum(JoinEnum.INNER)
                        .joinColumnAnnFiled(ResultVo::getCardInfo)
                        .build()
        );
        //
        /*studentWrapper.join(
                JoinQueryBuilder.<ScoreInfo, Student, ResultVo>builder()
                        .joinEnum(JoinEnum.INNER)
                        .mainJoinField(Student::getNo)
                        .joinField(ScoreInfo::getStuNo)
                        .build()
        );*/


        // 查询指定字段
//        studentWrapper.select(Student::getNo);
//        scoreWrapper.select(ScoreInfo::getScore, ScoreInfo::getStuScore);
        // 查询条件
        studentWrapper.eq(Student::getId, 2);

        // 排序
//        studentWrapper.orderByAsc(Student::getSex);

        return baseMapper.getResultVo(studentWrapper);
    }
    /**
     * 联查多条测试，返回为其他类
     */
    public List<ResultVo> joinListOtherClassTest() {
        // 主表wrapper
        JoinLambdaQueryWrapper<Student> studentWrapper = joinQueryWrapperForOther(ResultVo.class);
        // 副表wrapper
        JoinLambdaQueryWrapper<CardInfo> cardWrapper = studentWrapper.join(
                JoinQueryBuilder.<CardInfo, Student, ResultVo>builder()
                        .joinEnum(JoinEnum.INNER)
                        .joinColumnAnnFiled(ResultVo::getCardInfo)
                        .build()
        );
//        JoinLambdaQueryWrapper<ScoreInfo> scoreWrapper = studentWrapper.innerJoin(ScoreInfo.class, Student::getNo, ScoreInfo::getStuNo);

        /*JoinLambdaQueryWrapper<RewardInfo> rewardWrapper = studentWrapper.join(
                JoinQueryBuilder.<RewardInfo,Student,ResultVo>builder()
                        .joinEnum(JoinEnum.LEFT)
                        .mainJoinField(Student::getNo)
                        .joinField(RewardInfo::getStuNo)
                        .build()
        );*/


        // 查询指定字段
//        studentWrapper.select(Student::getNo);
//        scoreWrapper.select(ScoreInfo::getScore, ScoreInfo::getStuScore);
        // 查询条件
        studentWrapper.in(Student::getId, Arrays.asList(2, 13));

        // 排序
        studentWrapper.orderByAsc(Student::getSex);

        return baseMapper.listResultVos(studentWrapper);
    }

    /**
     * 分页查询测试
     */
    public Page<Student> joinPageTest(PageParam<Student> pageParam) {

        // 主表wrapper
        JoinLambdaQueryWrapper<Student> studentWrapper = joinQueryWrapperForTable();
        // 副表wrapper
        JoinLambdaQueryWrapper<ScoreInfo> scoreWrapper = studentWrapper.join(
                JoinQueryBuilder.<ScoreInfo, Student, Student>builder()
                        .joinEnum(JoinEnum.INNER)
                        .joinColumnAnnFiled(Student::getScore)
                        .build()
        );


        // 查询指定字段
//        studentWrapper.select(Student::getNo);
//        scoreWrapper.select(ScoreInfo::getScore, ScoreInfo::getStuScore);
        // 查询条件
//        studentWrapper.in(Student::getId, Arrays.asList(2, 13));

        // 排序
        scoreWrapper.orderByDesc(ScoreInfo::getScore);

        return baseMapper.selectJoinPage(pageParam, studentWrapper);
    }

    /**
     * 分页查询测试
     */
    public Page<ResultVo> joinTargetPageTest(PageParam<Student> pageParam) {

        // 主表wrapper
        JoinLambdaQueryWrapper<Student> studentWrapper = joinQueryWrapperForOther(ResultVo.class);
        // 副表wrapper
        JoinLambdaQueryWrapper<ScoreInfo> scoreWrapper = studentWrapper.join(
                JoinQueryBuilder.<ScoreInfo, Student, ResultVo>builder()
                        .joinEnum(JoinEnum.INNER)
                        .mainJoinField(Student::getNo)
                        .joinField(ScoreInfo::getStuNo)
                        .build()
        );

        JoinLambdaQueryWrapper<CardInfo> cardWrapper = studentWrapper.join(
                JoinQueryBuilder.<CardInfo, Student, ResultVo>builder()
                        .joinEnum(JoinEnum.INNER)
                        .joinColumnAnnFiled(ResultVo::getCardInfo)
                        .build()
        );

        // 查询指定字段
//        studentWrapper.select(Student::getNo);
//        scoreWrapper.select(ScoreInfo::getScore, ScoreInfo::getStuScore);
        // 查询条件
//        studentWrapper.in(Student::getId, Arrays.asList(2, 13));

        // 排序
        scoreWrapper.orderByDesc(ScoreInfo::getScore);

        return baseMapper.selectJoinResultPage(pageParam, studentWrapper);
    }

    /**
     * 获取单条记录并且拼接原生sql
     */
    public Student getOneByOriginSql() {
        return getOne(
                getMyLambdaQuery()
                        .eq(Student::getId, 2)
                        .isNotNull(Student::getName)
                        .originSelectSql("count(%s > 1 or null)", Student::getSex)
                        .originWhereSql("CHAR_LENGTH(%s) > 0",
                                getOriginSqlBuilder()
                                        .sFunction(Student::getName)
                                        .isColumn(true).build()
                        )
        );
    }

    /**
     * 修改记录并且拼接原生sql
     */
    public boolean updateOneByOriginSql() {
        return update(
                getMyLambdaUpdate()
                        .set(Student::getSex, 1)
                        .eq(Student::getId, 2)
                        .isNotNull(Student::getName)
                        .originWhereSql("CHAR_LENGTH(%s) > 0",
                                getOriginSqlBuilder()
                                        .sFunction(Student::getName)
                                        .isColumn(true).build()
                        )
        );
    }


    /**
     * case update
     */
    public boolean updateCase() {

        Map<Object, Object> updateMap = new HashMap<>();
        updateMap.put(2, 2);
        updateMap.put(13, 1);

        /**
         * UPDATE student
         * SET sex = (case id WHEN 2 THEN 2 WHEN 13 THEN 1 end)
         * WHERE (id IN (2, 13))
         */
        return update(
                getMyLambdaUpdate()
                        .caseSet(Student::getSex, updateMap, Student::getId)

        );
    }


    public Student getById(){
        return getOne(
                getMyLambdaQuery()
                        .gt(Student::getId, 2)
                        .sum(Student::getSex).as("sex")

        );
    }

    public Object joinQueryXml() {
        return null;
    }

}
